﻿using MediatR;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SheepCultivation.Common.Domain.main;
using SheepCultivation.Common.Domain.SheepBasicInformation;
using SheepCultivation.Common.Domain.Week2;
using SheepCultivation.Common.Dto;
using SheepCultivation.Common.Infrastructure.Interface;
using SheepCultivation.Enum;
using System.Globalization;
using NPOI;

namespace SheepCultivation.API.AddliCation.SheepFile
{
    public class SheepFileImportHandler : IRequestHandler<SheepFileImportCommon, Result>
    {
        public readonly IBaseRepository<BasicInformation> BasicInformationRepository;
        public readonly IBaseRepository<BirthMessage> BirthMessageRepository;
        public readonly IBaseRepository<Sheepbreed> SheepbreedRepository;
        public readonly IBaseRepository<BirthStatus> BirthStatusRepository;
        public readonly IBaseRepository<Transfer> TransferRepository;
        public readonly IBaseRepository<Building> BuildingRepository;
        public readonly IBaseRepository<Columns> ColumnsRepository;

        public SheepFileImportHandler(IBaseRepository<BasicInformation> basicInformationRepository, IBaseRepository<BirthMessage> birthMessageRepository, IBaseRepository<Sheepbreed> sheepbreedRepository, IBaseRepository<BirthStatus> birthStatusRepository, IBaseRepository<Transfer> transferRepository, IBaseRepository<Building> buildingRepository, IBaseRepository<Columns> columnsRepository)
        {
            BasicInformationRepository = basicInformationRepository;
            BirthMessageRepository = birthMessageRepository;
            SheepbreedRepository = sheepbreedRepository;
            BirthStatusRepository = birthStatusRepository;
            TransferRepository = transferRepository;
            BuildingRepository = buildingRepository;
            ColumnsRepository = columnsRepository;
        }

        public async Task<Result> Handle(SheepFileImportCommon request, CancellationToken cancellationToken)
        {
            var result = new Result() { Code = (int)CommonEnumCode.error };
            try
            {
                //获取文件扩展名 判断上传的文件类型是否正确
                var ext = Path.GetExtension(request.file.FileName);
                if (ext == ".xls" || ext == ".xlsx")
                {

                }
                else
                {
                    result.Msg = "您上传的文件类型不正确";
                    return result;
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    request.file.CopyTo(ms);
                    ms.Position = 0;
                    //根据文件扩展名创建对应的工作簿对象(HSSFWorkbook用于.xls，XSSFWorkbook用于.xlsx)
                    //获取工作簿
                    IWorkbook wb = null;
                    if (ext == ".xls")//创建97-2003
                    {
                        wb = new HSSFWorkbook(ms);
                    }
                    else//创建2007及以上版本
                    {
                        wb = new XSSFWorkbook(ms);
                    }
                    //获取sheet 获取工作簿中的第一个工作表
                    ISheet sheet = wb.GetSheetAt(0);
                    int totalRows = sheet.LastRowNum;//总行数
                    int totalCells = sheet.GetRow(0).LastCellNum;//总列数

                    // 创建一个用于存储解析后的数据的列表
                    var BasicInformationList = new List<BasicInformation>();
                    var BirthMessageList = new List<BirthMessage>();
                    //循环总行数 开头必须是1 不然从表头开始
                    //遍历工作表的每一行（从第二行开始，因为第一行通常是表头）
                    for (int i = 1; i <= totalRows; i++)
                    {
                        //获取当前行
                        IRow row = sheet.GetRow(i);

                        //这个类必须写到循环里边 不然你批量添加只能添加一行
                        var dto = new BasicInformation();// 在循环内创建新的对象实例
                        var dto1 = new BirthMessage();
                        //下标从0开始 导入时 外键需进行判断 客户不知道你是外键 只会输入文字 需要在后台判断
                       /* var earnumber = row.GetCell(0).ToString();
                        var basic = BasicInformationRepository.GetAll().Where(z => z.Auricle == earnumber).FirstOrDefault();
                        dto.BreedingSheepArchiveId = basic.BreedingSheepArchiveId;*/

                        //日期转换
                        //日期在excel表中必须是年月日格式 但不能加时分秒 (注意!!!!)
                        string inputDate = row.GetCell(5).ToString();
                        string[] multipleFormats = { "dd-M月-yyyy", "yyyy-M-dd" };
                        DateTime parsedDate;
                        foreach (var format in multipleFormats)
                        {
                            if (DateTime.TryParseExact(inputDate, new[] { format }, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDate))
                            {
                                dto1.BirthDate = Convert.ToDateTime(parsedDate.ToString("yyyy-MM-dd"));
                                break;//成功解析后跳出循环
                            }
                        }

                        dto.Auricle = row.GetCell(1).ToString();
                        dto.CorpuscleAuricle = row.GetCell(2).ToString();
                        dto.SheepbreedId = SheepbreedRepository.GetAll().Where(x => x.SheepbreedName == row.GetCell(3).ToString()).FirstOrDefault().SheepbreedId;
                        dto.SheepSex = row.GetCell(4).ToString() == "公" ? 1 : 2;
                        dto.SheepStatus = row.GetCell(6).ToString() == "待入场" ? 0 : 1;
                        dto.SheepTypeId = row.GetCell(7).ToString() == "种公" ? 1 : row.GetCell(7).ToString() == "种母" ? 2 : 0;
                        dto.BuildingId =
                            BuildingRepository.GetAll().Where(x => x.BuildingName == row.GetCell(8).ToString()).FirstOrDefault().BuildingId;
                        dto.ColumnsId =
                             ColumnsRepository.GetAll().Where(x => x.ColumnsName == row.GetCell(9).ToString()).FirstOrDefault().ColumnsId;
                        dto.GeneLV = row.GetCell(10).ToString()=="后备级"?1: row.GetCell(10).ToString() == "核心级" ? 2: row.GetCell(10).ToString() == "生产级" ? 3:4;
                        dto1.BirthWeight =Convert.ToDecimal( row.GetCell(11).ToString());
                        dto1.DadBreedingSheepArchiveId =
                             BasicInformationRepository.GetAll().Where(x => x.Auricle == row.GetCell(12).ToString()).FirstOrDefault().BreedingSheepArchiveId;
                        dto1.MomBreedingSheepArchiveId = BasicInformationRepository.GetAll().Where(x => x.Auricle == row.GetCell(13).ToString()).FirstOrDefault().BreedingSheepArchiveId;
                        var res = BasicInformationRepository.Add(dto);
                        dto1.BreedingSheepArchiveId = dto.BreedingSheepArchiveId;
                        dto1.BirthStatusId = 7;
                        BirthMessageList.Add(dto1);
                        
                    }
                   var res1 = BirthMessageRepository.AddRange(BirthMessageList);
                    //判断
                    if (res1 > 0)
                    {
                        result.Msg = "导入成功";
                    }
                    else
                    {
                        result.Msg = "导入失败";
                    }
                }
                result.Code = (int)CommonEnumCode.success;
            }
            catch (Exception)
            {
                throw;
            }

            return result;
        }
    }
}
